﻿Imports System.Data.SqlClient
Imports Crystal.DatabaseManagement.BusinessLayer
Imports Microsoft.Practices.EnterpriseLibrary.Data
Public Class cMaster

    Public Function Donotupdateifchangednotmade(ByVal tablename As String, ByVal id As Integer, ByVal firstparameter As String, Optional ByVal secondparameter As String = "", Optional ByVal thirdparameter As String = "") As Integer
        Dim baseclass As BaseClassDataContext = Nothing
        Dim totalrecords As Integer = 0
        Dim description As String = ""
        'Try
        baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
        Select Case tablename.ToLower.ToString().Trim()
            Case "city"
                totalrecords = (From p In baseclass.GetTable(Of CP_City)() _
                            Where p.CityID = id And p.City = firstparameter Select p).Count

            Case "client contact type"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_ClientContactType)() _
                          Where p.ContactTypeID = id And p.ContactType = firstparameter Select p).Count

            Case "client rating"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_ClientRating)() _
                           Where p.RatingID = id And p.Rating = firstparameter And p.RatingColor = secondparameter Select p).Count

            Case "client exchange"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_Exchange)() _
                           Where p.ExhangeID = id And p.Exchange = firstparameter Select p).Count

            Case "industry"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_Industry)() _
                            Where p.IndustryID = id And p.Industry = firstparameter Select p).Count

            Case "client invoice method"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_InvoiceMethod)() _
                           Where p.InvoiceID = id And p.Method = firstparameter Select p).Count

            Case "client sector"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_Sector)() _
                           Where p.SectorID = id And p.Sector = firstparameter Select p).Count

            Case "country"
                totalrecords = (From p In baseclass.GetTable(Of Cp_Country)() _
                           Where p.CountryID = id And p.CountryCode = firstparameter And p.CountryName = secondparameter And p.RegionID = thirdparameter Select p).Count

            Case "currency"
                totalrecords = (From p In baseclass.GetTable(Of CP_Currency)() _
                           Where p.CurrencyID = id And p.Currency = firstparameter And p.Symbol = secondparameter Select p).Count

            Case "fund type"
                description = (From p In baseclass.GetTable(Of Cp_FundType)() _
                           Where p.FundTypeID = id Select desc = p.Description).SingleOrDefault
                description = IIf(description Is Nothing, "", description)
                If (description.ToString().Trim = "" And secondparameter.Trim = "") Then
                    totalrecords = (From p In baseclass.GetTable(Of Cp_FundType)() _
                           Where p.FundTypeID = id And p.FundType = firstparameter Select p).Count
                Else
                    totalrecords = (From p In baseclass.GetTable(Of Cp_FundType)() _
                           Where p.FundTypeID = id And p.FundType = firstparameter And p.Description = secondparameter Select p).Count
                End If

            Case "investor type"
                totalrecords = (From p In baseclass.GetTable(Of Cp_InvestorType)() _
                           Where p.InvestorTypeID = id And p.MInvestorTypeID = firstparameter And p.InvestorType = secondparameter And p.Active = thirdparameter Select p).Count

            Case "master investor type"
                totalrecords = (From p In baseclass.GetTable(Of CP_MasterInvestorType)() _
                           Where p.MInvestorTypeID = id And p.MInvestorType = firstparameter And p.Active = secondparameter Select p).Count

            Case "metro"
                totalrecords = (From p In baseclass.GetTable(Of CP_Metro)() _
                           Where p.MetroId = id And p.Metro = firstparameter Select p).Count

            Case "title"
                totalrecords = (From p In baseclass.GetTable(Of CP_MST_Title)() _
                           Where p.TitleID = id And p.Title = firstparameter Select p).Count

            Case "parent"
                totalrecords = (From p In baseclass.GetTable(Of CP_Parent)() _
                           Where p.ParentID = id And p.Parent = firstparameter Select p).Count

            Case "product type"
                totalrecords = (From p In baseclass.GetTable(Of CP_PRJ_ProductType)() _
                           Where p.ProductTypeID = id And p.ProductType = firstparameter And p.ProductCode = secondparameter Select p).Count

            Case "project status"
                totalrecords = (From p In baseclass.GetTable(Of CP_PRJ_ProjectStatus)() _
                           Where p.ProjectStatusID = id And p.ProjectStatus = firstparameter Select p).Count

            Case "region"
                totalrecords = (From p In baseclass.GetTable(Of CP_Region)() _
                           Where p.RegionID = id And p.RegionCode = firstparameter And p.Region = secondparameter Select p).Count

            Case "state"
                totalrecords = (From p In baseclass.GetTable(Of CP_State)() _
                           Where p.StateID = id And p.State = firstparameter Select p).Count

            Case "style"
                description = (From p In baseclass.GetTable(Of CP_Style)() _
                           Where p.StyleID = id Select desc = p.Description).SingleOrDefault
                description = IIf(description Is Nothing, "", description)
                If (description.ToString().Trim = "" And secondparameter.Trim = "") Then
                    totalrecords = (From p In baseclass.GetTable(Of CP_Style)() _
                           Where p.StyleID = id And p.Style = firstparameter Select p).Count
                Else
                    totalrecords = (From p In baseclass.GetTable(Of CP_Style)() _
                      Where p.StyleID = id And p.Style = firstparameter And p.Description = secondparameter Select p).Count
                End If

            Case "task status"
                totalrecords = (From p In baseclass.GetTable(Of CP_TaskStatusLookup)() _
                           Where p.StatusId = id And p.Status = firstparameter Select p).Count

            Case "territory"
                totalrecords = (From p In baseclass.GetTable(Of CP_Territory)() _
                           Where p.TerritoryID = id And p.Territory = firstparameter Select p).Count

            Case "turnover"
                description = (From p In baseclass.GetTable(Of CP_Turnover)() _
                           Where p.TurnoverID = id Select desc = p.Description).SingleOrDefault
                description = IIf(description Is Nothing, "", description)
                If (description.ToString().Trim() = "" And secondparameter.Trim = "") Then
                    totalrecords = (From p In baseclass.GetTable(Of CP_Turnover)() _
                    Where p.TurnoverID = id And p.Turnover = firstparameter Select p).Count
                Else
                    totalrecords = (From p In baseclass.GetTable(Of CP_Turnover)() _
                    Where p.TurnoverID = id And p.Turnover = firstparameter And p.Description = secondparameter Select p).Count
                End If


            Case "ultimate parent"
                totalrecords = (From p In baseclass.GetTable(Of CP_UltimateParent)() _
                           Where p.UltimateParentID = id And p.UltimateParent = firstparameter Select p).Count

            Case "groups"
                totalrecords = (From p In baseclass.GetTable(Of Cp_Group)() _
                           Where p.GroupID = id And p.GroupName = firstparameter And p.IsMaster = secondparameter Select p).Count
            Case "factset"
                totalrecords = (From p In baseclass.GetTable(Of CP_FactsetFormat)() _
                           Where p.FormatID = id And p.FormatName = firstparameter Select p).Count
            Case "fund"
                totalrecords = (From p In baseclass.GetTable(Of CP_FundImportFormats)() _
                           Where p.FormatID = id And p.FormatName = firstparameter Select p).Count
            Case "investor"
                totalrecords = (From p In baseclass.GetTable(Of CP_InvestorImportFormats)() _
                           Where p.FormatID = id And p.FormatName = firstparameter Select p).Count
            Case "register"
                totalrecords = (From p In baseclass.GetTable(Of CP_RegisterFormats)() _
                           Where p.FormatID = id And p.FormatName = firstparameter Select p).Count
                'code for column table
            Case "source name"
                totalrecords = (From p In baseclass.GetTable(Of CP_Column)() _
                           Where p.Columnid = id And p.Columnname = firstparameter Select p).Count
                '*************************************************
        End Select
        Return totalrecords
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function
    ''' <summary>
    ''' returns the total record in a master table
    ''' </summary>
    ''' <param name="tablename"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetTotalRecords(ByVal tablename As String) As Integer
        Dim baseclass As BaseClassDataContext = Nothing
        Dim totalrecords As Integer = 0
        ' Try
        baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
        Select Case tablename.ToLower.ToString().Trim()
            Case "city"
                totalrecords = (From p In baseclass.GetTable(Of CP_City)() _
                            Select p).Count

            Case "client contact type"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_ClientContactType)() _
                            Select p).Count

            Case "client rating"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_ClientRating)() _
                            Select p).Count

            Case "client exchange"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_Exchange)() _
                            Select p).Count

            Case "industry"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_Industry)() _
                            Select p).Count

            Case "client invoice method"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_InvoiceMethod)() _
                            Select p).Count

            Case "client sector"
                totalrecords = (From p In baseclass.GetTable(Of CP_CLM_Sector)() _
                            Select p).Count

            Case "country"
                totalrecords = (From p In baseclass.GetTable(Of Cp_Country)() _
                            Select p).Count

            Case "currency"
                totalrecords = (From p In baseclass.GetTable(Of CP_Currency)() _
                            Select p).Count

            Case "fund type"
                totalrecords = (From p In baseclass.GetTable(Of Cp_FundType)() _
                            Select p).Count

            Case "investor type"
                totalrecords = (From p In baseclass.GetTable(Of Cp_InvestorType)() _
                            Select p).Count

            Case "master investor type"
                totalrecords = (From p In baseclass.GetTable(Of CP_MasterInvestorType)() _
                            Select p).Count

            Case "metro"
                totalrecords = (From p In baseclass.GetTable(Of CP_Metro)() _
                            Select p).Count

            Case "title"
                totalrecords = (From p In baseclass.GetTable(Of CP_MST_Title)() _
                            Select p).Count

            Case "parent"
                totalrecords = (From p In baseclass.GetTable(Of CP_Parent)() _
                            Select p).Count

            Case "product type"
                totalrecords = (From p In baseclass.GetTable(Of CP_PRJ_ProductType)() _
                            Select p).Count

            Case "project status"
                totalrecords = (From p In baseclass.GetTable(Of CP_PRJ_ProjectStatus)() _
                            Select p).Count

            Case "region"
                totalrecords = (From p In baseclass.GetTable(Of CP_Region)() _
                            Select p).Count

            Case "state"
                totalrecords = (From p In baseclass.GetTable(Of CP_State)() _
                            Select p).Count

            Case "style"
                totalrecords = (From p In baseclass.GetTable(Of CP_Style)() _
                            Select p).Count

            Case "task status"
                totalrecords = (From p In baseclass.GetTable(Of CP_TaskStatusLookup)() _
                            Select p).Count

            Case "territory"
                totalrecords = (From p In baseclass.GetTable(Of CP_Territory)() _
                            Select p).Count

            Case "turnover"
                totalrecords = (From p In baseclass.GetTable(Of CP_Turnover)() _
                            Select p).Count

            Case "ultimate parent"
                totalrecords = (From p In baseclass.GetTable(Of CP_UltimateParent)() _
                            Select p).Count

            Case "groups"
                totalrecords = (From p In baseclass.GetTable(Of Cp_Group)() _
                            Select p).Count
            Case "listing"
                totalrecords = (From p In baseclass.GetTable(Of CP_ProjectListing)() _
                            Select p).Count
            Case "column header"
                totalrecords = (From p In baseclass.GetTable(Of CP_SourceEntities)() _
                            Select p).Count
            Case "factset"
                totalrecords = (From p In baseclass.GetTable(Of CP_FactsetFormat)() _
                            Select p).Count
            Case "fund"
                totalrecords = (From p In baseclass.GetTable(Of CP_FundImportFormats)() _
                            Select p).Count
            Case "investor"
                totalrecords = (From p In baseclass.GetTable(Of CP_InvestorImportFormats)() _
                            Select p).Count
            Case "register"
                totalrecords = (From p In baseclass.GetTable(Of CP_RegisterFormats)() _
                            Select p).Count
                'Calculating the total records from the source entities
            Case "source name"
                totalrecords = (From p In baseclass.GetTable(Of CP_Column)() _
                            Select p).Count
                '*************************************************
        End Select
        Return totalrecords
        'Catch ex As Exception
        '    Return 0
        'End Try

    End Function

    ''' <summary>
    ''' before going to delete/deit the record it checks whether teh record exist
    ''' </summary>
    ''' <param name="tablename"></param>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckRecordExistence(ByVal tablename As String, ByVal id As Integer) As Boolean
        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_master_checkrecordexistence"
        ' Try
        eReturnVal = CreateDataBase().ExecuteDataSet("cp_sp_master_checkrecordexistence", New Object() {tablename, id})
        If (eReturnVal.Tables(0).Rows.Count > 0) Then
            Return False
        Else
            Return True
        End If

        'Catch ex As Exception
        '    Return False
        'End Try
    End Function

    ''' <summary>
    ''' fetch one record from the database
    ''' </summary>
    ''' <param name="tablename"></param>
    ''' <param name="autocolumnname"></param>
    ''' <param name="autocolumnval"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getdata(ByVal tablename As String, ByVal autocolumnname As String, ByVal autocolumnval As String)

        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_sel_mastertabledata"
        'Try
        eReturnVal = CreateDataBase().ExecuteDataSet("cp_sp_sel_mastertabledata", New Object() {tablename, autocolumnname, autocolumnval})

        Return eReturnVal.Tables(0)
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="Listingname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DeleteColumns(ByVal Id As String) As Boolean
        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_master_checkbeforedelete"
        'Try
        eReturnVal = CreateDataBase().ExecuteDataSet("CP_sp_MST_DEL_ProjectListing", New Object() {Id})
        If (eReturnVal.Tables(0).Rows(0).Item(0).ToString().ToLower() = "1") Then
            Return True
        Else
            Return False
        End If

        'Catch ex As Exception
        '    Return False
        'End Try
    End Function

    'Created the function for dleteing the listing from complete database.
    ''' <summary>
    ''' checks the reference of the record before deleting
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DeleteListings(ByVal Listingname As String) As Boolean
        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_master_checkbeforedelete"
        'Try
        eReturnVal = CreateDataBase().ExecuteDataSet("CP_sp_MST_DEL_ProjectListing", New Object() {Listingname})
        If (eReturnVal.Tables(0).Rows(0).Item(0).ToString().ToLower() = "1") Then
            Return True
        Else
            Return False
        End If

        'Catch ex As Exception
        '    Return False
        'End Try
    End Function

    ''' <summary>
    ''' checks the reference of the record before deleting
    ''' </summary>
    ''' <param name="tablename"></param>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function checkbeforedelete(ByVal tablename As String, ByVal id As String) As Boolean
        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_master_checkbeforedelete"
        'Try
        eReturnVal = CreateDataBase().ExecuteDataSet("cp_sp_master_checkbeforedelete", New Object() {tablename, id})
        If (eReturnVal.Tables(0).Rows(0).Item(0).ToString().ToLower() = "already exist") Then
            Return False
        Else
            Return True
        End If

        'Catch ex As Exception
        '    Return False
        'End Try
    End Function

    ''' <summary>
    ''' Set the region of combo
    ''' </summary>
    ''' <param name="list"></param>
    ''' <param name="statusid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SetIndexByLinq(ByVal list As DataTable, ByVal statusid As Integer) As Integer
        'Dim query = list
        'Dim baseclass As BaseClassDataContext = Nothing
        'Try
        For i As Integer = 0 To list.Rows.Count - 1
            If (list.Rows(i).Item("regionid").ToString() = statusid.ToString()) Then
                Return i
            End If
        Next

        Return 0
        'Catch ex As Exception
        '    Return 0
        'End Try

    End Function

    ''' <summary>
    ''' Checks the record duplicacy
    ''' </summary>
    ''' <param name="tablename"></param>
    ''' <param name="colname"></param>
    ''' <param name="colval"></param>
    ''' <param name="autocolname"></param>
    ''' <param name="autocolval"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckDuplicacy(ByVal tablename As String, ByVal colname As String, ByVal colval As String, ByVal autocolname As String, ByVal autocolval As String) As DataTable

        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_sel_mastertable_checkduplicate2"
        'Try
        eReturnVal = CreateDataBase().ExecuteDataSet("cp_sp_sel_mastertable_checkduplicate2", New Object() {tablename, colname, colval.Trim().Replace("'", "''"), autocolname, autocolval})

        Return eReturnVal.Tables(0)
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    Public Function CheckDuplicacy(ByVal tablename As String, ByVal colname As String, ByVal colvalOld As String, ByVal colvalNew As String) As DataTable

        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_sel_mastertable_checkduplicate2"
        'Try
        eReturnVal = CreateDataBase().ExecuteDataSet("CP_sp_CheckDuplicateEntries", New Object() {tablename, colname, colvalOld.Trim().Replace("'", "''"), colvalNew.Trim().Replace("'", "''")})

        Return eReturnVal.Tables(0)
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    ''' Return the region records
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectRegion() As DataTable
        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_master_sel_region"
        ' Try
        eReturnVal = CreateDataBase().ExecuteDataSet("cp_sp_master_sel_region")

        Return eReturnVal.Tables(0)
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    ''' Select the master investor type record
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectMasterInvestorType() As DataTable
        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "CP_Sp_INV_SEL_Firms"
        'Try
        eReturnVal = CreateDataBase().ExecuteDataSet("CP_Sp_INV_SEL_Firms")

        Return eReturnVal.Tables(0)
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    ''' Check the duplicacy for country table
    ''' </summary>
    ''' <param name="countrycode"></param>
    ''' <param name="countryname"></param>
    ''' <param name="countryid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckDulpicateCountryandregion(ByVal countrycode As String, ByVal countryname As String, ByVal countryid As String) As Boolean
        Dim baseclass As BaseClassDataContext = Nothing
        ' Try
        baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
        If (countryid.ToString().Trim() <> "") Then
            Dim query = (From p In baseclass.GetTable(Of Cp_Country)() _
                Where (p.CountryCode = countrycode And p.CountryName = countryname) And p.CountryID <> countryid).SingleOrDefault
            If (Not query Is Nothing) Then
                Return False
            End If
        Else
            Dim query = (From p In baseclass.GetTable(Of Cp_Country)() _
                  Where (p.CountryCode = countrycode And p.CountryName = countryname)).SingleOrDefault
            If (Not query Is Nothing) Then
                Return False
            End If
        End If
        Return True
        'Catch ex As Exception
        '    Return False
        'End Try
    End Function

    ''' <summary>
    ''' check the duplicacy for investor type
    ''' </summary>
    ''' <param name="investorname"></param>
    ''' <param name="investorid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckDulpicateINvestorType(ByVal minv As String, ByVal investorname As String, ByVal investorid As String) As Boolean
        Dim baseclass As BaseClassDataContext = Nothing
        'Try
        baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
        If (investorid.ToString().Trim() <> "") Then
            Dim query = (From p In baseclass.GetTable(Of Cp_InvestorType)() _
                  Where p.InvestorType = investorname And p.InvestorTypeID <> investorid And p.MInvestorTypeID = minv).SingleOrDefault
            If (Not query Is Nothing) Then
                Return False
            End If
        Else
            Dim query = (From p In baseclass.GetTable(Of Cp_InvestorType)() _
                  Where p.InvestorType = investorname And p.MInvestorTypeID = minv).SingleOrDefault
            If (Not query Is Nothing) Then
                Return False
            End If
        End If
        Return True
        'Catch ex As Exception
        '    Return False
        'End Try
    End Function

    ''' <summary>
    ''' check teh duplicate currency
    ''' </summary>
    ''' <param name="currency"></param>
    ''' <param name="symbol"></param>
    ''' <param name="currencyid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckDulpicateCurrency(ByVal currency As String, ByVal symbol As String, ByVal currencyid As String) As Boolean
        Dim baseclass As BaseClassDataContext = Nothing
        Dim query As New List(Of Integer)
        'Try
        baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
        If (currencyid = "") Then
            query = (From p In baseclass.GetTable(Of CP_Currency)() _
                         Where p.Currency = currency _
                         Select Cid = p.CurrencyID).ToList
            If (query.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Else
            query = (From p In baseclass.GetTable(Of CP_Currency)() _
                         Where (p.Currency = currency) _
                          And p.CurrencyID <> currencyid Select Cid = p.CurrencyID).ToList
            If (query.Count = 0) Then
                Return True
            Else
                Return False
            End If
        End If
        'Catch ex As Exception
        '    Return False
        'End Try
    End Function

    ''' <summary>
    ''' checking the duplicate decords
    ''' </summary>
    ''' <param name="tablename"></param>
    ''' <param name="colname1"></param>
    ''' <param name="colval1"></param>
    ''' <param name="colname2"></param>
    ''' <param name="colval2"></param>
    ''' <param name="autocolname"></param>
    ''' <param name="autocolval"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckDuplicacy1(ByVal tablename As String, ByVal colname1 As String, ByVal colval1 As String, ByVal colname2 As String, ByVal colval2 As String, ByVal autocolname As String, ByVal autocolval As String) As DataTable

        Dim eReturnVal As DataSet = Nothing
        'Const SEL_TestProcedure = "cp_sp_sel_mastertable_checkduplicate3"
        ' Try
        eReturnVal = CreateDataBase().ExecuteDataSet("cp_sp_sel_mastertable_checkduplicate3", New Object() {tablename, colname1, colval1.Trim().Replace("'", "''"), colname2, colval2.Trim().Replace("'", "''"), autocolname, autocolval})

        Return eReturnVal.Tables(0)
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    '''  'To Select Reference Table
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectReferenceTable() As ExecutionResult
        Dim eReturnVal As New ExecutionResult
        ' Try

        eReturnVal = CoreFunctions.Execute(strConfiguration, "Cp_SelReferenceTable", CommandType.StoredProcedure, ReturnTypes.DataSet)

        Return eReturnVal
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    ''' select the client rating
    ''' </summary>
    ''' <param name="rating"></param>
    ''' <param name="color"></param>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function selectClientRating(ByVal rating As String, ByVal color As String, ByVal id As String) As Boolean

        ' Try
        Dim baseclass As BaseClassDataContext = Nothing
        baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
        Dim query
        If (String.IsNullOrEmpty(id)) Then
            id = "0"
            query = (From p In baseclass.GetTable(Of CP_CLM_ClientRating)() _
                      Where (p.RatingColor = color Or p.Rating = rating) Select p).ToList
        Else
            query = (From p In baseclass.GetTable(Of CP_CLM_ClientRating)() _
                  Where (p.RatingColor = color Or p.Rating = rating) And Not p.RatingID = id Select p).ToList

        End If
        If (query.count = 0) Then
            Return True
        Else
            Return False
        End If
       
        If (query Is Nothing) Then
            Return Nothing
        Else
            Return True
        End If
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    '''  'To Select Reference Table Data
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectReferenceTableData(ByVal strCol As String, ByVal rowid As Integer) As DataTable
        Dim ds As DataSet = Nothing
        Dim storedprocedurename As String = "CP_SEL_ReferenceTableData"
        Try

            ds = CreateDataBase().ExecuteDataSet(storedprocedurename, New Object() {strCol, rowid})
            If ds.Tables.Count Then
                Return ds.Tables(0)
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    ''' <summary>
    '''  'To Select Reference Table Column Info
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectReferenceTableColumnInfo(ByVal strCol As String) As ExecutionResult
        Dim eReturnVal As New ExecutionResult
        Dim param(0) As SqlClient.SqlParameter
        'Try
        param(0) = New SqlParameter("@TableName", strCol)
        eReturnVal = CoreFunctions.Execute(strConfiguration, "CP_SEL_ReferenceTableInfo", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
        Return eReturnVal
        'Catch ex As Exception
        '    Throw ex
        'End Try
    End Function

    ''' <summary>
    '''  'To Insert/Update/Delete Reference Table Column Info
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsUpdDelReferenceTable(ByVal strAction As String, ByVal strTableName As String, ByVal strPart1 As String, ByVal strPart2 As String, Optional ByRef autoid As String = "0") As Boolean
        Dim storedprocedurename As String = "CP_INS_UPD_Reference_Table"
        Dim cmd As SqlCommand = Nothing
        Dim param As SqlClient.SqlParameter

        '        Try
        cmd = New SqlCommand(GlobalConst.ConnectionString)
        cmd.CommandText = storedprocedurename
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@ACTION", strAction)
        cmd.Parameters.AddWithValue("@TABLENAME", strTableName)
        cmd.Parameters.AddWithValue("@PART1", strPart1)
        cmd.Parameters.AddWithValue("@PART2", strPart2)
        param = New SqlParameter("@newlyGenegatedAutoId", SqlDbType.Int)
        param.Direction = ParameterDirection.Output
        cmd.Parameters.Add(param)
        CreateDataBase().ExecuteNonQuery(cmd)
        autoid = cmd.Parameters("@newlyGenegatedAutoId").Value.ToString()
       
        Return True
        'Catch ex As Exception
        '    Return False
        'Finally
        '    param = Nothing
        'End Try
    End Function
End Class
